﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Data;
using System.Globalization;
using NPOI.HSSF.Record;
using NPOI.HSSF.Model;
using NPOI.DDF;
using System.Collections;

namespace CommonLib
{
    /// <summary>
    /// NPOI封装类
    /// </summary>
    public class ExcelOperate
    {
        #region Fields
        private HSSFWorkbook workbook;
        private string FileName;
        #endregion

        #region Construct
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="fileName">EXCEL路径</param>
        public ExcelOperate(string fileName)
        {
            if (!File.Exists(fileName))
            {
                throw new Exception(string.Format("文件'{0}'不存在", fileName));
            }
            this.FileName = fileName;
            using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                workbook = new HSSFWorkbook(file);
                file.Close();
            }

            //SaveData();
        }
        public ExcelOperate(HSSFWorkbook workbook)
        {
            this.workbook = workbook;
        }
        #endregion

        #region WorkBook
        /// <summary>
        /// 设置默认样式
        /// </summary>
        /// <returns></returns>
        private ICellStyle GetDefaultCellStyle()
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 9;
            font.FontName = "Arial";
            cellStyle.SetFont(font);
            return cellStyle;

        }
        /// <summary>
        /// SHEET自适应
        /// </summary>
        private void SheeFitToPage()
        {
            for (int i = 0; i < this.workbook.NumberOfSheets; i++)
            {
                ISheet sheet = this.workbook.GetSheetAt(i);
                if (sheet == null)
                    continue;
                sheet.FitToPage = true;
            }
        }
        /// <summary>
        /// 保存到文件
        /// </summary>
        /// <param name="path"></param>
        /// <param name="fileMode"></param>
        public string WorkBookSaveToFile(string path)
        {
            try
            {
                string dir = Path.GetDirectoryName(path);
                if (!Path.IsPathRooted(dir))
                {
                    dir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, dir);
                }
                if (!Directory.Exists(dir))
                {
                    Directory.CreateDirectory(dir);
                }
                string fileName = Path.Combine(dir, Path.GetFileName(path));
                FileStream fs = new FileStream(fileName, FileMode.Create);
                //SheeFitToPage();
                workbook.Write(fs);
                fs.Close();
                return fileName;
            }
            catch
            {
                return "";
            }

        }
        public void Save()
        {
            FileStream fs = new FileStream(this.FileName, FileMode.Create);
            workbook.Write(fs);
            fs.Close();
        }
        public void Save(string fileName)
        {
            string dir = Path.GetDirectoryName(fileName);
            if (!Path.IsPathRooted(dir))
            {
                dir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, dir);
            }
            if (!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }
            fileName = Path.Combine(dir, Path.GetFileName(fileName));
            FileStream fs = new FileStream(fileName, FileMode.Create);
            workbook.Write(fs);
            fs.Close();
        }
        /// <summary>
        /// 跨WORKBOOK 复制SHEET
        /// 该方法不支持复杂格式
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <param name="sheetIndexs">SHEET索引数组</param>
        public void CopySheetFromFile(string fileName, int[] sheetIndexs)
        {
            HSSFWorkbook fromwork;
            using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                fromwork = new HSSFWorkbook(file);
                file.Close();
            }
            HSSFSheet fromSheet = ((HSSFSheet)fromwork.GetSheetAt(0)).CloneSheet(fromwork);
            HSSFSheet toSheet = (HSSFSheet)workbook.CreateSheet();
            toSheet = fromSheet;


        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="ToCellInfo"></param>
        public void CopyDataFromFile(string fileName, CellInfo ToCellInfo)
        {
            //还需做调整
            if (!File.Exists(fileName)) return;
            HSSFWorkbook fromwork;
            using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                fromwork = new HSSFWorkbook(file);
                file.Close();
            }
            //CellInfo fromCellInfo = FindIndexByName("CV_START", fromwork);
            ///默认读取第一个SHEET
            ISheet Fromsheet = fromwork.GetSheetAt(0);
            if (Fromsheet == null) return;
            int firstRowIndex = Fromsheet.FirstRowNum;
            int firstColIndex = Fromsheet.GetRow(Fromsheet.FirstRowNum).FirstCellNum;
            int iFromsubToRowIndex = firstRowIndex - ToCellInfo.RowIndex;
            int iFromsubToColIndex = firstColIndex - ToCellInfo.ColIndex;
            Dictionary<int, int> dicCellPostion = new Dictionary<int, int>();

            ISheet ToSheet = workbook.GetSheetAt(ToCellInfo.SheetIndex);
            if (ToSheet == null) return;

            //复制Merge单元格 格式Start
            int sheetMergerCount = Fromsheet.NumMergedRegions;
            for (int i = 0; i < sheetMergerCount; i++)
            {

                int iFirstColumn = Fromsheet.GetMergedRegion(i).FirstColumn - iFromsubToColIndex;
                int iFirstRow = Fromsheet.GetMergedRegion(i).FirstRow - iFromsubToRowIndex;
                int iLastColumn = Fromsheet.GetMergedRegion(i).LastColumn - iFromsubToColIndex;
                int iLastRow = Fromsheet.GetMergedRegion(i).LastRow - iFromsubToRowIndex;

                CellRangeAddress region = new CellRangeAddress(iFirstRow, iLastRow, iFirstColumn, iLastColumn);

                ToSheet.AddMergedRegion(region);

            }
            //复制Merge单元格 格式End

            int lastRowNum = Fromsheet.LastRowNum;
            int iToRow = 0;
            for (int i = firstRowIndex; i < lastRowNum; i++)
            {
                IRow Fromrow = Fromsheet.GetRow(i);
                IRow Torow = ToSheet.CreateRow(ToCellInfo.RowIndex + iToRow);
                int lastColNum = Fromrow.LastCellNum;
                int FirstColNum = firstColIndex;
                if (Fromrow != null)
                {
                    for (int j = FirstColNum; j < lastColNum; j++)
                    {
                        //取得要加载的文件的内容
                        if (Fromrow.GetCell(j) == null) continue;
                        string value = Fromrow.GetCell(j).StringCellValue;

                        ICellStyle FromCellStyle = Fromrow.GetCell(j).CellStyle;
                        ICellStyle ToCellStyle = (ICellStyle)workbook.CreateCellStyle();
                        ToCellStyle.CloneStyleFrom(FromCellStyle);
                        ICell CreateCell = Torow.CreateCell(ToCellInfo.ColIndex + j - FirstColNum);

                        //赋值    
                        CreateCell.SetCellValue(value);
                        //设备表格格式
                        CreateCell.CellStyle = ToCellStyle;
                    }
                }
                iToRow++;
            }
        }
        #endregion

        #region Sheet
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheetIndex"></param>
        /// <param name="rowIndex"></param>
        /// <param name="colIndex"></param>
        /// <param name="picFileName"></param>
        /// <param name="width"></param>
        /// <param name="height"></param>
        public void AddPicture(int sheetIndex, int rowIndex, int colIndex, string picFileName, int width, int height)
        {
            if (!File.Exists(picFileName))
            {
                throw new Exception("图片不存在");
            }
            if (rowIndex < 0)
            {
                throw new Exception("行不存在");
            }
            if (colIndex < 0)
            {
                throw new Exception("列不存在");
            }
            byte[] bytes = System.IO.File.ReadAllBytes(picFileName);
            int picIndex = this.workbook.AddPicture(bytes, PictureType.JPEG);
            //获取存在的Sheet，必须在AddPicture之后   
            ISheet sheet = workbook.GetSheetAt(sheetIndex);
            IDrawing patriarch = sheet.CreateDrawingPatriarch();

            //插入图片   
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, colIndex, rowIndex, colIndex + width, rowIndex + height);
            IPicture pict1 = patriarch.CreatePicture(anchor, picIndex);
            //pict1.Resize();
        }
        /// <summary>
        /// 添加图片
        /// </summary>
        /// <param name="name">添加位置的命名</param>
        /// <param name="picFileName">图片路径</param>
        /// <param name="width">宽</param>
        /// <param name="height">高</param>
        public void AddPicture(string name, string picFileName, int width, int height)
        {
            CellInfo info = FindIndexByName(name);
            if (info == null) return;
            if (info.RowIndex < 0) return;
            if (!File.Exists(picFileName))
            {
                throw new Exception("图片不存在");
            }
            if (info.RowIndex < 0)
            {
                throw new Exception("行不存在");
            }

            byte[] bytes = System.IO.File.ReadAllBytes(picFileName);
            int picIndex = this.workbook.AddPicture(bytes, PictureType.JPEG);
            //获取存在的Sheet，必须在AddPicture之后   
            ISheet sheet = workbook.GetSheet(info.SheetName);
            IDrawing patriarch = sheet.CreateDrawingPatriarch();

            //插入图片   
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, info.ColIndex, info.RowIndex, info.ColIndex + width, info.RowIndex + height);
            IPicture pict1 = patriarch.CreatePicture(anchor, picIndex);
            //pict1.Resize();
        }
        /// <summary>
        /// 根据DATATABLE数据添加到EXCEL
        /// 根据列名定位
        /// </summary>
        /// <param name="dt">数据集</param>
        /// <param name="rowHeight">行高</param>
        public void AddRows(DataTable dt, int rowHeight)
        {

            List<CellInfo> cells = new List<CellInfo>();
            foreach (DataColumn dc in dt.Columns)
            {
                CellInfo info = FindIndexByName(dc.Caption);
                if (info.RowIndex < 0)
                    continue;
                cells.Add(info);
            }
            if (cells.Count < 1) return;
            ISheet sheet = workbook.GetSheet(cells[0].SheetName);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                IRow row = sheet.GetRow((i + 1) * rowHeight + cells[0].RowIndex);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    DataColumn dc = dt.Columns[j];
                    CellInfo info = cells.Find(p => p.Name == dc.Caption);
                    if (info == null)
                        continue;
                    if (info.ColIndex < 0)
                        continue;
                    ICell cell = row.GetCell(info.ColIndex);
                    cell.SetCellValue(dr[dc.Caption].ToString());
                }

            }
        }


        public void InsertExcelRow(int Index, int Row, int Col, int InsertCount)
        {
            InsertExcelRow(Index, Row, Col, InsertCount, false);
        }
        public void InsertExcelRow(int Index, int Row, int Col, int InsertCount, bool merged)
        {
            ISheet sheet = this.workbook.GetSheetAt(Index);
            sheet.ShiftRows(Row + 1, sheet.LastRowNum, InsertCount, true, false);
            IRow RouceseRow = sheet.GetRow(Index);

            MovePictureAnchor((HSSFSheet)sheet, Row + 1, InsertCount);
            try
            {
                if (RouceseRow != null)
                    for (int i = Row + 1; i < Row + InsertCount; i++)
                    {
                        IRow rowInsert = sheet.GetRow(i);
                        if (merged)
                        {
                            MergedRegion(Index, i, i, 0, RouceseRow.LastCellNum);
                        }
                        rowInsert.RowStyle = RouceseRow.RowStyle;
                        rowInsert.Height = RouceseRow.Height;

                        for (int j = 0; j < RouceseRow.LastCellNum; j++)
                        {
                            ICell RouceseCol = RouceseRow.GetCell(j);
                            ICell InsertCol = rowInsert.GetCell(j);
                            InsertCol.CellStyle = RouceseCol.CellStyle;
                        }
                    }
            }
            catch { }
        }

        /// 合并单元格
        /// </summary>
        /// <param name="sheetIndex">sheet索引</param>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        public void MergedRegion(int sheetIndex, int rowstart, int rowend, int colstart, int colend)
        {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            ISheet sheet = this.workbook.GetSheetAt(sheetIndex);
            if (sheet != null)
                sheet.AddMergedRegion(cellRangeAddress);
        }
        /// <summary>
        /// 设置行高
        /// </summary>
        /// <param name="sheetIndex">SHEET索引</param>
        /// <param name="rowIndex">行</param>
        /// <param name="height">高度</param>
        public void SetRowHeight(int sheetIndex, int rowIndex, short height)
        {
            ISheet sheet = this.workbook.GetSheetAt(sheetIndex);
            if (sheet != null)
            {
                IRow row = sheet.GetRow(rowIndex);
                if (row != null)
                    row.Height = height;
            }
        }


        public void SetCellValue(string sheetName, int rowIndex, int colIndex, string value)
        {
            SetCellValue(sheetName, rowIndex, colIndex, value, false, false);
        }
        public void SetCellValue(string sheetName, int rowIndex, int colIndex, string value, bool defaultCellStyle)
        {
            SetCellValue(sheetName, rowIndex, colIndex, value, defaultCellStyle, false);
        }
        public void SetCellValue(string sheetName, int rowIndex, int colIndex, string value,
            bool defaultCellStyle, bool wrapText)
        {
            ICellStyle cs = null;
            if (defaultCellStyle)
            {
                cs = GetDefaultCellStyle();
            }
            ISheet sheet = this.workbook.GetSheet(sheetName);
            if (sheet != null)
            {
                //sheet.CreateRow(rowIndex).CreateCell(colIndex).SetCellValue(value);
                IRow row = sheet.GetRow(rowIndex);
                if (row == null)
                {
                    row = sheet.CreateRow(rowIndex);
                }
                ICell cell = row.GetCell(colIndex);
                if (cell == null)
                {

                    cell = row.CreateCell(colIndex);

                }
                if (cs != null)
                {
                    cell.CellStyle = cs;
                }
                if (wrapText)
                {
                    ICellStyle style3 = this.workbook.CreateCellStyle();
                    style3.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
                    style3.VerticalAlignment = VerticalAlignment.TOP;
                    style3.WrapText = true;//自动换行
                    cell.CellStyle = style3;

                }
                cell.SetCellValue(value);

            }
        }
        public CellInfo SetCellValueByName(string name, string value)
        {
            return SetCellValueByName(name, value, false);
        }
        /// <summary>
        /// 根据名称设置单元格的值
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns>行号和列号</returns>
        public CellInfo SetCellValueByName(string name, string value, bool wrapText)
        {
            int[] result = new int[2];
            //int colIndex = 0;
            //int rowIndex = 0;
            //int sheetIndex = 0;
            CellInfo cellInfo = FindIndexByName(name);
            if (cellInfo.RowIndex < 0)
            { return null; }
            ISheet sheet = this.workbook.GetSheetAt(cellInfo.SheetIndex);
            if (sheet != null)
            {
                IRow row = sheet.GetRow(cellInfo.RowIndex);
                if (row != null)
                {
                    ICell cell = row.GetCell(cellInfo.ColIndex);
                    if (cell != null)
                    {
                        if (wrapText)
                        {
                            ICellStyle style3 = this.workbook.CreateCellStyle();
                            style3.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
                            style3.VerticalAlignment = VerticalAlignment.TOP;
                            style3.WrapText = true;//自动换行
                            cell.CellStyle = style3;

                        }
                        cell.SetCellValue(value);
                        return cellInfo;
                    }
                }
            }
            return null;
        }
        /// <summary>
        /// 根据名称返回单元格的位置信息
        /// </summary>
        /// <param name="name"></param>
        public CellInfo FindIndexByName(string name)
        {
            CellInfo cellInfo = new CellInfo();
            cellInfo.Name = name;
            cellInfo.RowIndex = -1;
            cellInfo.ColIndex = -1;

            IName iname = this.workbook.GetName(name);
            if (iname == null)
                return cellInfo;
            string sheetName = iname.SheetName;
            cellInfo.SheetName = sheetName;
            cellInfo.SheetIndex = workbook.GetSheetIndex(sheetName);// iname.SheetIndex;
            string sName = sheetName + "!";

            if (iname.RefersToFormula.StartsWith(sName)
                || iname.RefersToFormula.StartsWith("'" + sheetName + "'!"))
            {
                string range = iname.RefersToFormula.Replace(sName, "");
                string[] rs = range.Split('$');

                if (rs.Length > 2)
                {
                    string sColIndex = rs[1];
                    string sRowIndex = rs[2];
                    int colIndex = -1;
                    int rowIndex = -1;
                    char[] cColIndexs = sColIndex.ToCharArray();
                    ///转换EXCEL列号ABCD，变成数字索引
                    switch (cColIndexs.Length)
                    {
                        case 1:
                            colIndex = Convert.ToInt32(cColIndexs[0]) - 65;
                            break;
                        case 2:
                            int a = (Convert.ToInt32(cColIndexs[0]) - 64) * 26;
                            int b = Convert.ToInt32(cColIndexs[1]) - 65;
                            colIndex = a + b;
                            break;
                        case 3:
                            a = (Convert.ToInt32(cColIndexs[0]) - 64) * 26 * 26;
                            b = (Convert.ToInt32(cColIndexs[1]) - 64) * 26;
                            int c = Convert.ToInt32(cColIndexs[2]) - 65;
                            colIndex = a + b + c;
                            break;
                    }
                    rowIndex = Convert.ToInt32(sRowIndex) - 1;
                    cellInfo.RowIndex = rowIndex;
                    cellInfo.ColIndex = colIndex;
                }


            }

            return cellInfo;
        }

        public CellInfo FindIndexByName(string name, HSSFWorkbook Work)
        {
            CellInfo cellInfo = new CellInfo();
            cellInfo.Name = name;
            cellInfo.RowIndex = -1;
            cellInfo.ColIndex = -1;

            IName iname = Work.GetName(name);
            if (iname == null)
                return cellInfo;
            string sheetName = iname.SheetName;
            cellInfo.SheetName = sheetName;
            cellInfo.SheetIndex = Work.GetSheetIndex(sheetName);// iname.SheetIndex;
            string sName = sheetName + "!";

            if (iname.RefersToFormula.StartsWith(sName)
                || iname.RefersToFormula.StartsWith("'" + sheetName + "'!"))
            {
                string range = iname.RefersToFormula.Replace(sName, "");
                string[] rs = range.Split('$');

                if (rs.Length > 2)
                {
                    string sColIndex = rs[1];
                    string sRowIndex = rs[2];
                    int colIndex = -1;
                    int rowIndex = -1;
                    char[] cColIndexs = sColIndex.ToCharArray();
                    ///转换EXCEL列号ABCD，变成数字索引
                    switch (cColIndexs.Length)
                    {
                        case 1:
                            colIndex = Convert.ToInt32(cColIndexs[0]) - 65;
                            break;
                        case 2:
                            int a = (Convert.ToInt32(cColIndexs[0]) - 64) * 26;
                            int b = Convert.ToInt32(cColIndexs[1]) - 65;
                            colIndex = a + b;
                            break;
                        case 3:
                            a = (Convert.ToInt32(cColIndexs[0]) - 64) * 26 * 26;
                            b = (Convert.ToInt32(cColIndexs[1]) - 64) * 26;
                            int c = Convert.ToInt32(cColIndexs[2]) - 65;
                            colIndex = a + b + c;
                            break;
                    }
                    rowIndex = Convert.ToInt32(sRowIndex) - 1;
                    cellInfo.RowIndex = rowIndex;
                    cellInfo.ColIndex = colIndex;
                }


            }

            return cellInfo;
        }


        /// <summary>
        /// 移动SHEET中的图片
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="startRow"></param>
        /// <param name="MoveRowStep"></param>
        /// <returns></returns>
        private int MovePictureAnchor(HSSFSheet sheet, int startRow, int MoveRowStep)
        {
            int count = 0;
            try
            {
                IDrawing patriarch = sheet.CreateDrawingPatriarch();
                IList pictures = workbook.GetAllPictures();
                HSSFPatriarch p = patriarch as HSSFPatriarch;
                List<HSSFShape> shapes = p.Children as List<HSSFShape>;

                foreach (HSSFShape shap in shapes)
                {

                    HSSFClientAnchor anchor = (HSSFClientAnchor)shap.Anchor;
                    int rowIndex = anchor.Row1;
                    if (shap is HSSFPicture)
                    {
                        int rowmark = rowIndex;
                        HSSFPicture picture = (HSSFPicture)shap;
                        int pictureIndex = picture.PictureIndex;
                        if (rowIndex >= startRow)
                        {
                            anchor.Row1 = anchor.Row1 + MoveRowStep;
                            anchor.Row2 = anchor.Row2 + MoveRowStep;
                            shap.Anchor = anchor;
                            count++;
                        }
                    }
                }
                return count;
            }
            catch
            {
                return -1;
            }

        }
        /// <summary>
        /// 移动SHEET中的图片
        /// EG:  MovePicture(sheet as HSSFSheet, 0, 1, new HSSFClientAnchor(0, 0, 0, 0, 8, 5, 12, 7));
        /// </summary>
        /// <param name="sheet">SHEET对象</param>
        /// <param name="row">图片所在行</param>
        /// <param name="col">图片所在列</param>
        /// <param name="moveTo">新的位置</param>
        /// <returns>图片在WORKBOOK中的索引</returns>
        private int MovePicture(HSSFSheet sheet, int row, int col, int MoveRowStep)
        {
            IDrawing patriarch = sheet.CreateDrawingPatriarch();
            IList pictures = workbook.GetAllPictures();
            HSSFPatriarch p = patriarch as HSSFPatriarch;
            List<HSSFShape> shapes = p.Children as List<HSSFShape>;
            foreach (HSSFShape shap in shapes)
            {

                HSSFClientAnchor anchor = (HSSFClientAnchor)shap.Anchor;
                int rowIndex = anchor.Row1;
                if (shap is HSSFPicture)
                {
                    int rowmark = rowIndex;
                    HSSFPicture picture = (HSSFPicture)shap;
                    int pictureIndex = picture.PictureIndex;
                    if (rowIndex == row && anchor.Col1 == col)
                    {
                        anchor.Row1 = anchor.Row1 + MoveRowStep;
                        shap.Anchor = anchor;
                        return pictureIndex;
                    }
                }
            }

            return -1;
        }
        public void SetPageHeader(int sheetIndex, PageHeader header, PageHeader footer)
        {
            ISheet sheet = this.workbook.GetSheetAt(sheetIndex);

            HSSFSheet s1 = (HSSFSheet)sheet;

            if (header != null)
            {
                if (!string.IsNullOrEmpty(header.Left))
                    s1.Header.Left = header.Left;
                if (!string.IsNullOrEmpty(header.Center))
                    s1.Header.Center = header.Center;
                if (!string.IsNullOrEmpty(header.Right))
                    s1.Header.Right = header.Right;

            }
            if (footer != null)
            {
                if (!string.IsNullOrEmpty(footer.Left))
                    s1.Footer.Left = footer.Left;
                if (!string.IsNullOrEmpty(footer.Center))
                    s1.Footer.Center = footer.Center;
                if (!string.IsNullOrEmpty(footer.Right))
                    s1.Footer.Right = footer.Right;
            }

        }
        public void setSheetName(int Index, string SheetName)
        {
            workbook.SetSheetName(Index, SheetName);
        }
        /// <summary>
        /// 获取页数
        /// </summary>
        /// <param name="sheetIndex"></param>
        /// <returns></returns>
        public int GetPageNumber(int sheetIndex)
        {
            ISheet sheet = this.workbook.GetSheetAt(sheetIndex);
            return sheet.RowBreaks.Length;
            // return Convert.ToInt32(page);
        }
        public void setCellStyle(int sheetIndex, int Row, int Col, string Value)
        {
            HSSFCellStyle CellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            CellStyle.BorderBottom = BorderStyle.THIN;
            CellStyle.BorderLeft = BorderStyle.THIN;
            CellStyle.BorderRight = BorderStyle.THIN;
            CellStyle.BorderTop = BorderStyle.THIN;
            CellStyle.FillForegroundColor = HSSFColor.YELLOW.index;
            CellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;

            ISheet sheet = this.workbook.GetSheetAt(sheetIndex);
            //sheet.SetRowBreak
            if (sheet != null)
            {
                IRow row = sheet.GetRow(Row);
                //sheet.SetColumnWidth(Col, 10*256);
                if (row != null)
                {
                    ICell cell = row.GetCell(Col);
                    if (cell != null)
                    {
                        cell.CellStyle = CellStyle;
                        cell.SetCellValue(Value);
                        return;
                    }
                }
            }
        }
        #endregion

        #region Copy Sheet




        /// <summary>
        /// 复制表
        /// </summary>
        /// <param name="fileName"></param>
        public void CopySheet(string fileName)
        {
            HSSFWorkbook from_wb;
            using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                from_wb = new HSSFWorkbook(file);
                file.Close();
            }
            if (from_wb == null) return;
            this.workbook.CloneSheet(from_wb, 0);
            //ISheet fromSheet = from_wb.GetSheetAt(0);
            //if (fromSheet == null) return;
            //ISheet toSheet = this.workbook.CreateSheet(fromSheet.SheetName);
            ////合并区域处理
            //MergerRegion(fromSheet, toSheet);
            /////始终只有一个CELLSTYPLE,避免超过样式数量4000报错
            //ICellStyle newstyle = this.workbook.CreateCellStyle();
            //System.Collections.IEnumerator rows = fromSheet.GetRowEnumerator();
            //int maxColumnNum = 0;
            //while (rows.MoveNext())
            //{
            //    IRow row = null;
            //    if (this.workbook is HSSFWorkbook)
            //        row = rows.Current as HSSFRow;
            //    else
            //    {
            //        throw new Exception("不支持的文件格式");
            //    }
            //    if (row.LastCellNum > maxColumnNum)
            //        maxColumnNum = row.LastCellNum;
            //    IRow newRow = toSheet.CreateRow(row.RowNum);
            //    CopyRow(row, newRow, newstyle);
            //}
            //for (int i = 0; i <= maxColumnNum; i++)
            //{
            //    toSheet.SetColumnWidth(i, fromSheet.GetColumnWidth(i));
            //}
            //toSheet.ForceFormulaRecalculation = true;
            //toSheet.PrintSetup.Landscape = fromSheet.PrintSetup.Landscape;
            //toSheet.PrintSetup.HResolution = fromSheet.PrintSetup.HResolution;
            //toSheet.PrintSetup.VResolution = fromSheet.PrintSetup.VResolution;
            //toSheet.SetMargin(MarginType.LeftMargin, fromSheet.GetMargin(MarginType.LeftMargin));
            //toSheet.SetMargin(MarginType.RightMargin, fromSheet.GetMargin(MarginType.RightMargin));
            //toSheet.SetMargin(MarginType.TopMargin, fromSheet.GetMargin(MarginType.TopMargin));
            //toSheet.SetMargin(MarginType.BottomMargin, fromSheet.GetMargin(MarginType.BottomMargin));
            //toSheet.PrintSetup.HeaderMargin = fromSheet.PrintSetup.HeaderMargin;
            //toSheet.PrintSetup.FooterMargin = fromSheet.PrintSetup.FooterMargin;
            //toSheet.Header.Left = fromSheet.Header.Left;
            //toSheet.Header.Center = fromSheet.Header.Center;
            //toSheet.Header.Right = fromSheet.Header.Right;
            //toSheet.Footer.Left = fromSheet.Footer.Left;
            //toSheet.Footer.Center = fromSheet.Footer.Center;
            //toSheet.Footer.Right = fromSheet.Footer.Right;
            //toSheet.PrintSetup.Scale = fromSheet.PrintSetup.Scale;
            //toSheet.PrintSetup.FitHeight = fromSheet.PrintSetup.FitHeight;
            //toSheet.PrintSetup.FitWidth = fromSheet.PrintSetup.FitWidth;

        }
       
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fromRow"></param>
        /// <param name="toRow"></param>
        /// <param name="newCellStyle"></param>
        /// <returns></returns>
        public void CopyRow(IRow fromRow, IRow toRow,ICellStyle newCellStyle)
        {
            System.Collections.IEnumerator cells = fromRow.GetEnumerator(); //.GetRowEnumerator();
            toRow.Height = fromRow.Height;
            while (cells.MoveNext())
            {
                ICell cell = null;
                //ICell cell = (wb is HSSFWorkbook) ? cells.Current as HSSFCell : cells.Current as NPOI.XSSF.UserModel.XSSFCell;
                if (this.workbook is HSSFWorkbook)
                    cell = cells.Current as HSSFCell;
                else
                {
                    throw new Exception("不支持的文件格式");
                }
                ICell newCell = toRow.CreateCell(cell.ColumnIndex); 
                CopyCell(cell, newCell,newCellStyle);
            }
        }


        /// <summary>
        /// 复制原有sheet的合并单元格到新创建的sheet
        /// </summary>
        /// <param name="fromSheet"></param>
        /// <param name="toSheet"></param>
        public void MergerRegion(ISheet fromSheet, ISheet toSheet)
        {
            int sheetMergerCount = fromSheet.NumMergedRegions;
            for (int i = 0; i < sheetMergerCount; i++)
            {
                toSheet.AddMergedRegion(fromSheet.GetMergedRegion(i));
            }
        }

        /// <summary>
        /// 复制单元格
        /// </summary>
        /// <param name="srcCell"></param>
        /// <param name="distCell"></param>
        public void CopyCell(ICell srcCell, ICell distCell, ICellStyle newCellStyle)
        {

            newCellStyle.CloneStyleFrom(srcCell.CellStyle);
            if (this.workbook.NumCellStyles < 4000)
            {
               
            }
            else
            {
               // ICellStyle fromStyle = srcCell.CellStyle;
               // newstyle.Alignment = fromStyle.Alignment;  
               // //边框和边框颜色   
               // newstyle.BorderBottom = fromStyle.BorderBottom;
               // newstyle.BorderLeft = fromStyle.BorderLeft;
               // newstyle.BorderRight = fromStyle.BorderRight;
               // newstyle.BorderTop = fromStyle.BorderTop;
               // newstyle.TopBorderColor = fromStyle.TopBorderColor;
               // newstyle.BottomBorderColor = fromStyle.BottomBorderColor;
               // newstyle.RightBorderColor = fromStyle.RightBorderColor;
               // newstyle.LeftBorderColor = fromStyle.LeftBorderColor;  
  
               ////背景和前景   
               // newstyle.FillBackgroundColor = fromStyle.FillBackgroundColor;
               // newstyle.FillForegroundColor = fromStyle.FillForegroundColor;

               // newstyle.DataFormat = fromStyle.DataFormat;
               // newstyle.FillPattern = fromStyle.FillPattern;  
               // //toStyle.Hidden=fromStyle.Hidden;   
               // newstyle.IsHidden = fromStyle.IsHidden;
               // newstyle.Indention = fromStyle.Indention;//首行缩进   
               // newstyle.IsLocked = fromStyle.IsLocked;
               // newstyle.Rotation = fromStyle.Rotation;//旋转   
               // newstyle.VerticalAlignment = fromStyle.VerticalAlignment;
               // newstyle.WrapText = fromStyle.WrapText;  

            }
            
            //样式
            //distCell.CellStyle = newCellStyle;
            //评论
            if (srcCell.CellComment != null)
            {
                distCell.CellComment = srcCell.CellComment;
            }
            // 不同数据类型处理
            CellType srcCellType = srcCell.CellType;
            distCell.SetCellType(srcCellType);

            if (srcCellType == CellType.NUMERIC)
            {

                if (HSSFDateUtil.IsCellDateFormatted(srcCell))
                {
                    distCell.SetCellValue(srcCell.DateCellValue);
                }
                else
                {
                    distCell.SetCellValue(srcCell.NumericCellValue);
                }
            }
            else if (srcCellType == CellType.STRING)
            {
                distCell.SetCellValue(srcCell.RichStringCellValue);
            }
            else if (srcCellType == CellType.BLANK)
            {
                // nothing21
            }
            else if (srcCellType == CellType.BOOLEAN)
            {
                distCell.SetCellValue(srcCell.BooleanCellValue);
            }
            else if (srcCellType == CellType.ERROR)
            {
                distCell.SetCellErrorValue(srcCell.ErrorCellValue);
            }
            else if (srcCellType == CellType.FORMULA)
            {
                distCell.SetCellFormula(srcCell.CellFormula);
            }
            else
            { // nothing29
            }

        }


        #endregion
    }





    public class CellInfo
    {
        public string Name
        { get; set; }
        public int SheetIndex
        { get; set; }
        public string SheetName
        { get; set; }
        public int RowIndex
        { get; set; }
        public int ColIndex
        { get; set; }

    }
    public class PageHeader
    {
        public string Center
        { get; set; }
        public string Left
        { get; set; }
        public string Right
        { get; set; }
    }
}
